Selecting Single Rows

Selecting single rows from a database is a common task. There are two primary driving cases:

• Firstly selecting data involved with a unique index such as a primary key, where there can be zero or one row returned.
• Secondly select the top row from a database with a specified order, for example selecting the person with the most sales, or selecting a person's most recent sale, In these cases, there may be zero, one or more rows returned.

Depending on how you want to work with the data that you retrieve there are several options when working with single-row data. These options focus on how you want to deal with the cases where there are zero, rows, one row and many rows. The default method is ExecuteTo<T>(cmd). This provides the default implementation of FirstOrDefault.

The examples below: we will be working with the Product table, and returning ProductSummary, The ProductID is the Primary Key to the Product Table.

ExecuteTo<T>

    public ProductSummary GetProductSummary(int productId)
    {
        var sql = @"Select ProductID, Name, ProductNumber, ReorderPoint, StandardCost, rowguid, ModifiedDate  
From  [Production].[Product]
where ProductID = @productId";
        var cmd = CreateTextCommand(sql).WithParameter(productId.ToSqlParameter("@productId"));
        return ExecuteTo<ProductSummary>(cmd);
    }

The ExecuteTo<T> is simply short form for Execute(cmd).ToFirstOrDefault;

Execute(cmd).ToFirstOrDefault<T>

    public ProductSummary GetProductSummary(int productId)
    {
        var sql = @"Select ProductID, Name, ProductNumber, ReorderPoint, StandardCost, rowguid, ModifiedDate  
From  [Production].[Product]
where ProductID = @productId";
        var cmd = CreateTextCommand(sql).WithParameter(productId.ToSqlParameter("@productId"));
        return Execute(cmd).ToFirstOrDefault<ProductSummary>();
    }

Execute(cmd).ToFirst<T>

    public ProductSummary GetProductSummary(int productId)
    {
        var sql = @"Select ProductID, Name, ProductNumber, ReorderPoint, StandardCost, rowguid, ModifiedDate  
From  [Production].[Product]
where ProductID = @productId";
        var cmd = CreateTextCommand(sql).WithParameter(productId.ToSqlParameter("@productId"));
        return Execute(cmd).ToFirst<ProductSummary>();
    }

Tips with the Execute(cmd).ToFirst<T>

• Use this method when there are one or many records returned from the database.
• If there are many records you can get the database to restrict data on the server using top x, This results in better performance

Execute(cmd).ToSingleOrDefault<T>

    public ProductSummary GetProductSummary(int productId)
    {
        var sql = @"Select ProductID, Name, ProductNumber, ReorderPoint, StandardCost, rowguid, ModifiedDate  
From  [Production].[Product]
where ProductID = @productId";
        var cmd = CreateTextCommand(sql).WithParameter(productId.ToSqlParameter("@productId"));
        return Execute<cmd>.ToSingleOrDefault<ProductSummary>(cmd);
    }

Tips with the Execute(cmd).ToSingleOrDefault<T>

• Use this method when there is zero or one record returned from the database.
• If there are many you can use restricted data on the database server using top x to get better performance

Execute(cmd).ToSingle

    public ProductSummary GetProductSummary(int productId)
    {
        var sql = @"Select ProductID, Name, ProductNumber, ReorderPoint, StandardCost, rowguid, ModifiedDate  
From  [Production].[Product]
where ProductID = @productId";
        var cmd = CreateTextCommand(sql).WithParameter(productId.ToSqlParameter("@productId"));
        return Execute<cmd>.ToSingle<ProductSummary>(cmd);
    }

Tips with the Execute(cmd).ToSingle<T>

• The execute single is designed to work you you are working with a Primary or unique key on a data set.
• If you are after the best performance the ToFirst is faster as it skips the check for the second row
• If there are many rows you can use restricted data on the database server using top x to get better performance

ExecuteDataRow(cmd)

This method will return a DataRow, the data row can be passed into the translator. This method is executed using a DbDataAdapter as such there is no Async support.

ExecuteObject(cmd)

This will execute to a dynamic object. This is useful for quick prototyping but provides no "Type Safty".

Tips

If you using this method it is best to understand the underlying SQL. When selecting via a unique key with no joins there can be zero or one row returned. however, when data is involved with joins or data is involved in selecting sets to get the best performance you need to use the SQL "top" or "limit" syntax.

• If you using First, or FirstOrDefault you can use Top 1 or LIMIT 1 depending on the database.
• If you are using Single or SingleOrDefault and what to check for no second data you can use Top 2 or LIMIT 2 depending on the database.
• Using Single with Top 1 will yield the same behavior as First